package com.yonyou.handler;

import com.yonyou.entity.ResultEntity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * Created by WangXin on 2017/5/24.
 */
public class CreateDB {
    static String sqlStr = "jdbc:mysql://localhost:3306/xml_test?useUnicode=true&characterEncoding=UTF-8";
    static String rootName = "root";
    static String rootPwd = "root";

    public static void writeToMysql(List<ResultEntity> resultEntityList) {
        //1.加载driver驱动
        try {
            // 加载MySql的驱动类
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("找不到驱动程序类 ，加载驱动失败！");
            e.printStackTrace();
        }
        //2.建立连接
        Statement st = null;
        //调用DriverManager对象的getConnection()方法，获得一个Connection对象
        Connection con = null;
        try {
            //建立数据库连接
            con = DriverManager.getConnection(sqlStr, rootName, rootPwd);
            //INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
            for (int i=1;i<resultEntityList.size();i++) {

                String sql = "insert into contractconfirm(" +
                        "INDEX_I," +
                        "PK_CONTRACTCONFIRM, " +
                        "PK_CORP, " +
                        "VBILLNO, " +
                        "DUTYNAME, " +
                        "CONTRACTTYPE, " +
                        "CONTRACTCONFIRMMNY, " +
                        "DBILLDATE, " +
                        "PK_BILLTYPE, " +
                        "VBILLSTATUS, " +
                        "CUSTOMSUBPROJECTNO, " +
                        "ACCEPTORG, " +
                        "ACCEPTDEPT, " +
                        "STARTTIME, " +
                        "ENDTIME, " +
                        "VOPERATORID, " +
                        "DMAKEDAT, " +
                        "VMODIFIERID, " +
                        "DMODIFYDATE, " +
                        "VAPPROVEID, " +
                        "DAPPROVEDATE, " +
                        "VAPPROVENOTE, " +
                        "VMEMO, " +
                        "DEF1, " +
                        "DEF2, " +
                        "DEF3, " +
                        "DEF4, " +
                        "DEF5, " +
                        "TS, " +
                        "DR, " +
                        "ISDIV) " +
                        "values(" +
                        "\"" + resultEntityList.get(i).getIndex()+ "\"," +
                        "       \"" + resultEntityList.get(i).getPk_contractconfirm()+ "\"," +
                        "       \"" + resultEntityList.get(i).getPk_corp()+ "\"," +
                        "       \"" + resultEntityList.get(i).getVbillno()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDutyname()+ "\"," +
                        "       \"" + resultEntityList.get(i).getContracttype()+ "\"," +
                        "       \"" + resultEntityList.get(i).getContractconfirmmny()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDbilldate()+ "\"," +
                        "       \"" + resultEntityList.get(i).getPk_billtype()+ "\"," +
                        "       \"" + resultEntityList.get(i).getVbillstatus()+ "\"," +
                        "       \"" + resultEntityList.get(i).getCustomsubprojectno()+ "\"," +
                        "       \"" + resultEntityList.get(i).getAcceptorg()+ "\"," +
                        "       \"" + resultEntityList.get(i).getAcceptdept()+ "\"," +
                        "       \"" + resultEntityList.get(i).getStarttime()+ "\"," +
                        "       \"" + resultEntityList.get(i).getEndtime()+ "\"," +
                        "       \"" + resultEntityList.get(i).getVoperatorid()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDmakedat()+ "\"," +
                        "       \"" + resultEntityList.get(i).getVmodifierid()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDmodifydate()+ "\"," +
                        "       \"" + resultEntityList.get(i).getVapproveid()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDapprovedate()+ "\"," +
                        "       \"" + resultEntityList.get(i).getVapprovenote()+ "\"," +
                        "       \"" + resultEntityList.get(i).getVmemo()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDef1()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDef2()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDef3()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDef4()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDef5()+ "\"," +
                        "       \"" + resultEntityList.get(i).getTs()+ "\"," +
                        "       \"" + resultEntityList.get(i).getDr()+ "\"," +
                        "       \"" + resultEntityList.get(i).getIsdiv()+ "\")";
                System.out.println(sql);
                st = con.createStatement(); //创建一个Statement对象
                st.executeUpdate(sql);//提交数据更新

            }
            //插入语句格式
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                st.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
